﻿using System.Collections.Generic;
using System.Data;
using System.Data.Common;

public class DataAccess
{
    private string constr = "";
    private DbConnection conn = null;
    private DbTransaction trans = null;
    private string _dbprovider = null;

    public void Open()
    {
        Open("System.Data.OleDb", System.Configuration.ConfigurationManager.ConnectionStrings["constr"].ConnectionString);
    }

    public void Open(string dbprovider)
    {
        Close();
        DbProviderFactory factory = DbProviderFactories.GetFactory(dbprovider);
        _dbprovider = dbprovider;
        conn = factory.CreateConnection();
        conn.ConnectionString = constr;
        conn.Open();
    }

    public void Open(string dbprovider, string str)
    {
        constr = str;
        Open(dbprovider);
    }

    public void Close()
    {
        if (null != trans)
        {
            trans.Dispose();
        }
        if (null != conn)
        {
            if (conn.State != ConnectionState.Closed)
            {
                conn.Close();
            }
            conn.Dispose();
        }
    }

    public DbTransaction BeginTransaction()
    {
        if (null != trans)
        {
            trans.Dispose();
        }
        trans = conn.BeginTransaction();
        return null;
    }

    public void Commit()
    {
        if (null != trans)
        {
            trans.Commit();
        }
    }

    public void RollBack()
    {
        if (null != trans)
        {
            trans.Rollback();
        }
    }

    public int ExeNonQuery(string command, Dictionary<object, object> pars)
    {
        int count = -1;
        using (DbCommand comm = conn.CreateCommand())
        {
            comm.CommandText = command;
            if (null != trans)
            {
                comm.Transaction = trans;
            }
            if (null != pars)
            {
                foreach (string key in pars.Keys)
                {
                    DbParameter par = comm.CreateParameter();
                    par.ParameterName = "@" + key;
                    par.Value = pars[key];
                    comm.Parameters.Add(par);
                }
            }
            count = comm.ExecuteNonQuery();
        }
        return count;
    }

    public int ExeNonStoreProcedure(string spname)
    {
        #region Comments
        //int count = -1;
        //using (DbCommand comm=conn.CreateCommand())
        //{
        //    comm.CommandText = spname;
        //    comm.CommandType = CommandType.StoredProcedure;
        //    if (null != trans)
        //    {
        //        comm.Transaction = trans;
        //    }
        //    count = comm.ExecuteNonQuery();
        //}
        //return count;
        #endregion
        return ExeNonStoreProcedure(spname, null);
    }

    public int ExeNonStoreProcedure(string spname, Dictionary<object, object> pars)
    {
        #region Comments
        //int count = -1;
        //using (DbCommand comm = conn.CreateCommand())
        //{
        //    comm.CommandText = spname;
        //    comm.CommandType = CommandType.StoredProcedure;
        //    if (null != pars)
        //    {
        //        foreach (string key in pars.Keys)
        //        {
        //            DbParameter par = comm.CreateParameter();
        //            par.ParameterName = "@" + key;
        //            par.Value = pars[key];
        //            comm.Parameters.Add(par);
        //        }
        //    }
        //    if (null != trans)
        //    {
        //        comm.Transaction = trans;
        //    }
        //    count = comm.ExecuteNonQuery();
        //}
        //return count;
        #endregion
        Dictionary<object, object> tmp = new Dictionary<object, object>();
        return ExeNonStoreProcedure(spname, pars, ref tmp);
    }

    public int ExeNonStoreProcedure(string spname, Dictionary<object, object> pars, ref Dictionary<object, object> opars)
    {
        int count = -1;
        using (DbCommand comm = conn.CreateCommand())
        {
            comm.CommandText = spname;
            comm.CommandType = CommandType.StoredProcedure;
            if (null != pars)
            {
                foreach (string key in pars.Keys)
                {
                    DbParameter par = comm.CreateParameter();
                    par.ParameterName = "@" + key;
                    par.Value = pars[key];
                    comm.Parameters.Add(par);
                }
            }
            if (null != opars && opars.Count > 0)
            {
                foreach (string key in opars.Keys)
                {
                    DbParameter par = comm.CreateParameter();
                    par.ParameterName = "@" + key;
                    par.Direction = ParameterDirection.Output;
                    comm.Parameters.Add(par);
                }
            }
            if (null != trans)
            {
                comm.Transaction = trans;
            }
            count = comm.ExecuteNonQuery();
            if (null != opars && opars.Count > 0)
            {
                foreach (string key in opars.Keys)
                {
                    opars[key] = comm.Parameters["@" + key].ToString();
                }
            }
        }
        return count;
    }

    public DataTable ExeDataTableStoreProcedure(string spname)
    {
        #region Comments
        //DataTable dt = new DataTable();
        //DbProviderFactory factory = DbProviderFactories.GetFactory(_dbprovider);
        //using (DbDataAdapter da=factory.CreateDataAdapter())
        //{
        //    da.SelectCommand = conn.CreateCommand();
        //    da.SelectCommand.CommandText = spname;
        //    da.SelectCommand.CommandType = CommandType.StoredProcedure;
        //    da.Fill(dt);
        //}
        //if (dt.Rows.Count > 0)
        //{
        //    return dt;
        //}
        //else
        //{
        //    return null;
        //}
        #endregion
        return ExeDataTableStoreProcedure(spname, null);
    }

    public DataTable ExeDataTableStoreProcedure(string spname, Dictionary<object, object> pars)
    {
        #region Comments
        //DataTable dt = new DataTable();
        //DbProviderFactory factory = DbProviderFactories.GetFactory(_dbprovider);
        //using (DbDataAdapter da = factory.CreateDataAdapter())
        //{
        //    da.SelectCommand = conn.CreateCommand();
        //    da.SelectCommand.CommandText = spname;
        //    da.SelectCommand.CommandType = CommandType.StoredProcedure;
        //    if (null != pars)
        //    {
        //        foreach (string key in pars.Keys)
        //        {
        //            DbParameter par = da.SelectCommand.CreateParameter();
        //            par.ParameterName = "@" + key;
        //            par.Value = pars[key];
        //            da.SelectCommand.Parameters.Add(par);
        //        }                    
        //    }
        //    da.Fill(dt);
        //}
        //if (dt.Rows.Count > 0)
        //{
        //    return dt;
        //}
        //else
        //{
        //    return null;
        //}
        #endregion
        Dictionary<object, object> tmp = new Dictionary<object, object>();
        return ExeDataTableStoreProcedure(spname, pars, ref tmp);
    }

    public DataTable ExeDataTableStoreProcedure(string spname, Dictionary<object, object> pars, ref Dictionary<object, object> opars)
    {
        DataTable dt = new DataTable();
        DbProviderFactory factory = DbProviderFactories.GetFactory(_dbprovider);
        using (DbDataAdapter da = factory.CreateDataAdapter())
        {
            da.SelectCommand = conn.CreateCommand();
            da.SelectCommand.CommandText = spname;
            da.SelectCommand.CommandType = CommandType.StoredProcedure;
            if (null != pars)
            {
                foreach (string key in pars.Keys)
                {
                    DbParameter par = da.SelectCommand.CreateParameter();
                    par.ParameterName = "@" + key;
                    par.Value = pars[key];
                    da.SelectCommand.Parameters.Add(par);
                }
            }
            if (null != opars && opars.Count > 0)
            {
                foreach (string key in opars.Keys)
                {
                    DbParameter par = da.SelectCommand.CreateParameter();
                    par.ParameterName = "@" + key;
                    par.Direction = ParameterDirection.Output;
                    da.SelectCommand.Parameters.Add(par);
                }
            }
            da.Fill(dt);

            if (null != opars && opars.Count > 0)
            {
                foreach (string key in opars.Keys)
                {
                    opars[key] = da.SelectCommand.Parameters["@" + key].ToString();
                }
            }
        }
        if (dt.Rows.Count > 0)
        {
            return dt;
        }
        else
        {
            return null;
        }
    }

    public DataTable ExeDataTable(string command, Dictionary<object, object> pars)
    {
        DataTable dt = new DataTable();
        DbProviderFactory factory = DbProviderFactories.GetFactory(_dbprovider);
        using (DbDataAdapter da = factory.CreateDataAdapter())
        {
            da.SelectCommand = conn.CreateCommand();
            da.SelectCommand.CommandText = command;
            if (null != trans)
            {
                da.SelectCommand.Transaction = trans;
            }
            if (null != pars)
            {
                foreach (string key in pars.Keys)
                {
                    DbParameter par = da.SelectCommand.CreateParameter();
                    par.ParameterName = "@" + key;
                    par.Value = pars[key];
                    da.SelectCommand.Parameters.Add(par);
                }
            }
            da.Fill(dt);
        }
        if (dt.Rows.Count > 0)
        {
            return dt;
        }
        else
        {
            return null;
        }
    }

    public object ExeScalar(string command, Dictionary<object, object> pars)
    {
        object obj = null;
        using (DbCommand comm = conn.CreateCommand())
        {
            comm.CommandText = command;
            if (null != trans)
            {
                comm.Transaction = trans;
            }
            if (null != pars)
            {
                foreach (string key in pars.Keys)
                {
                    DbParameter par = comm.CreateParameter();
                    par.ParameterName = "@" + key;
                    par.Value = pars[key];
                    comm.Parameters.Add(par);
                }
            }
            obj = comm.ExecuteScalar();
        }
        return obj;
    }
}

